/**
 * @Author: lena
 * @Description:CRUD操作数据库
 * @Version: 1.0.0
 * @Date: 2021/9/12 20:32
 */

package mysql

import (
	"fmt"
)

// 查询一条记录：QueryRow
func QueryOne(id int) Student {
	sql := "select * from student where id = ?"
	s := Student{}
	// 查询后需要用Scan将查询结果写入结构体中
	err := db.QueryRow(sql, id).Scan(&s.id, &s.name, &s.age)
	if err != nil {
		fmt.Println("select err :", err)
	}
	return s
}

// 查询多条记录
func QueryAgeMore(age int) []Student {
	sql := "select * from student where age > ?"
	rows, err := db.Query(sql, age)
	if err != nil {
		fmt.Println("select err :", err)
		return nil
	}
	defer rows.Close()
	var students []Student
	// 当有下一行时返回true
	for rows.Next() {
		var s Student
		err := rows.Scan(&s.id, &s.name, &s.age)
		if err != nil {
			fmt.Println("scan err :", err)
			break
		}
		students = append(students, s)
	}
	return students
}

// 写操作（增删改）都使用Exec方法
// 新增数据：返回自增的id
func Insert(s Student) int {
	sql := "insert into student(name,age) values(?,?)"
	res, err := db.Exec(sql, s.name, s.age)
	if err != nil {
		fmt.Println("insert err :", err)
		return 0
	}
	id, err := res.LastInsertId() // 返回的id属性列必须是自动递增的
	if err != nil {
		fmt.Println("get last id err :", err)
		return s.id
	}
	return int(id)
}

// 更新数据:返回影响行数
func UpdateOne(s Student) int64 {
	sql := "update student set age = ? where id = ?"
	res, err := db.Exec(sql, s.age, s.id)
	if err != nil {
		fmt.Println("update err :", err)
		return 0
	}
	i, err := res.RowsAffected()
	if err != nil {
		fmt.Println("get effected row num err :", err)
		return 0
	}
	return i
}

// 删除数据
func DeleteOne(id int) int {
	sql := "delete from student where id = ?"
	res, err := db.Exec(sql, id)
	if err != nil {
		fmt.Println("delete err :", err)
		return 0
	}
	i, err := res.RowsAffected()
	if err != nil {
		fmt.Println("get effected row num err :", err)
		return 0
	}
	return int(i)
}
